import pool from '../utils/db';
import { hashPassword } from '../utils/auth';
import { RowDataPacket, ResultSetHeader } from 'mysql2';

export interface User {
  id: number;
  username: string;
  password: string;
  email: string;
  avatar?: string;
  bio?: string;
  // 联系方式
  phone?: string;
  address?: string;
  website?: string;
  location?: string;
  company?: string;
  birth_date?: Date;
  // 教育背景
  education_level?: string;
  school?: string;
  major?: string;
  graduation_year?: number;
  // 职业信息
  title?: string;
  skills?: string;
  resume_url?: string;
  // 简历相关
  resume_title?: string;
  resume_description?: string;
  // 企业相关
  is_company_admin?: boolean;
  company_role?: string;
  company_verified?: boolean;
  created_at: Date;
  updated_at: Date;
  // 允许任意其他属性
  [key: string]: any;
}

export interface WorkExperience {
  id?: number;
  user_id: number;
  company: string;
  company_name?: string;
  position: string;
  start_date: Date | string;
  end_date?: Date | string;
  is_current?: boolean; // 修改为可选字段，因为在某些情况下可能未定义
  description?: string;
  responsibilities?: string;
  achievements?: string;
  created_at?: Date;
  updated_at?: Date;
  [key: string]: any;
}

export interface UserRegisterData {
  username: string;
  password: string;
  email: string;
  phone?: string;
}

export interface UserLoginData {
  username: string;
  password: string;
}

export interface UserProfileData {
  avatar?: string;
  bio?: string;
  phone?: string;
  address?: string;
  website?: string;
  location?: string;
  company?: string;
  birth_date?: string;
  education_level?: string;
  school?: string;
  major?: string;
  graduation_year?: number;
  title?: string;
  skills?: string;
  resume_url?: string;
}

// 已在上面定义过 WorkExperience 接口，这里不需要重复定义

export interface EducationExperience {
  id?: number;
  user_id: number;
  school: string;
  degree: string;
  field_of_study?: string;
  start_date?: Date | string;
  end_date?: Date | string;
  description?: string;
  created_at?: Date;
  updated_at?: Date;
}

export interface Skill {
  id?: number;
  user_id: number;
  name: string;
  proficiency?: number;
  description?: string;
  created_at?: Date;
  updated_at?: Date;
}

export interface Internship {
  id: number;
  user_id: number;
  company: string;
  position: string;
  period: string;
  description: string | null;
}

export interface Award {
  id: number;
  user_id: number;
  name: string;
  issuer: string;
  date: string;
  description: string | null;
}

export const UserModel = {
  // 创建用户
  async create(userData: UserRegisterData): Promise<number> {
    const { username, password, email, phone } = userData;
    const hashedPassword = await hashPassword(password);

    const [result] = await pool.execute(
      'INSERT INTO users (username, password, email, phone, created_at, updated_at) VALUES (?, ?, ?, ?, NOW(), NOW())',
      [username, hashedPassword, email, phone || null]
    );

    return (result as any).insertId;
  },

  // 通过用户名查找用户
  async findByUsername(username: string): Promise<User | null> {
    const [rows] = await pool.execute(
      'SELECT * FROM users WHERE username = ?',
      [username]
    );

    const users = rows as User[];
    return users.length > 0 ? users[0] : null;
  },

  // 通过邮箱查找用户
  async findByEmail(email: string): Promise<User | null> {
    const [rows] = await pool.execute(
      'SELECT * FROM users WHERE email = ?',
      [email]
    );

    const users = rows as User[];
    return users.length > 0 ? users[0] : null;
  },

  // 通过手机号查找用户
  async findByPhone(phone: string): Promise<User | null> {
    const [rows] = await pool.execute(
      'SELECT * FROM users WHERE phone = ?',
      [phone]
    );

    const users = rows as User[];
    return users.length > 0 ? users[0] : null;
  },

  // 通过ID查找用户
  async findById(id: number): Promise<User | null> {
    const [rows] = await pool.execute(
      'SELECT * FROM users WHERE id = ?',
      [id]
    );

    const users = rows as User[];
    return users.length > 0 ? users[0] : null;
  },

  // 更新用户资料
  async updateProfile(userId: number, profileData: UserProfileData): Promise<boolean> {
    try {
      console.log('更新用户资料:', { userId, profileData });
      console.log('出生日期:', profileData.birth_date);
      console.log('单位:', profileData.company);

      // 构建动态更新语句
      const updateFields: string[] = [];
      const updateValues: any[] = [];

      // 确保字段与数据库字段名称一致
      const validFields = [
        'username', 'email', 'avatar', 'bio', 'phone',
        'address', 'website', 'education_level', 'school',
        'major', 'graduation_year', 'title', 'skills',
        'resume_url', 'resume_title', 'resume_description', 'location',
        'company', 'birth_date'
      ];

      // 遍历profileData的每个属性
      for (const [key, value] of Object.entries(profileData)) {
        if (value !== undefined && validFields.includes(key)) {
          updateFields.push(`${key} = ?`);
          updateValues.push(value);
          console.log(`添加更新字段: ${key} = ${value}`);

          // 特别记录company字段的更新
          if (key === 'company') {
            console.log('正在处理company字段，值为:', value);
          }
        }
      }

      // 没有有效的更新字段，直接返回成功
      if (updateFields.length === 0) {
        console.log('没有有效的更新字段');
        return true;
      }

      // 添加必要的字段
      updateFields.push('updated_at = NOW()');

      // 构建完整的SQL语句
      const query = `UPDATE users SET ${updateFields.join(', ')} WHERE id = ?`;
      updateValues.push(userId); // 放在最后，用于WHERE条件

      console.log('SQL更新语句:', query);
      console.log('更新值:', updateValues);

      try {
        const [result] = await pool.execute(query, updateValues);
        console.log('SQL更新结果:', result);

        return (result as any).affectedRows > 0;
      } catch (sqlError) {
        console.error('SQL执行错误:', sqlError);
        throw new Error(`SQL执行错误: ${(sqlError as Error).message}`);
      }
    } catch (error) {
      console.error('更新用户资料错误:', error);
      throw error;
    }
  },

  // 获取用户的工作经历
  async getWorkExperiences(userId: number): Promise<WorkExperience[]> {
    const [rows] = await pool.execute(
      'SELECT * FROM work_experiences WHERE user_id = ? ORDER BY start_date DESC',
      [userId]
    );

    return rows as WorkExperience[];
  },

  // 添加工作经历
  async addWorkExperience(workExp: WorkExperience): Promise<number> {
    try {
      console.log('添加工作经历:', workExp);

      // 处理可能为undefined的字段
      const end_date = workExp.end_date || null;
      const description = workExp.description || null;
      const is_current = workExp.is_current === undefined ? false : workExp.is_current;

      const [result] = await pool.execute(
        `INSERT INTO work_experiences
         (user_id, company, position, start_date, end_date, is_current, description, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
        [
          workExp.user_id,
          workExp.company,
          workExp.position,
          workExp.start_date,
          end_date,
          is_current,
          description
        ]
      );

      return (result as any).insertId;
    } catch (error) {
      console.error('添加工作经历失败:', error);
      throw error;
    }
  },

  // 更新工作经历
  async updateWorkExperience(workExpId: number, workExp: Partial<WorkExperience>): Promise<boolean> {
    try {
      console.log('更新工作经历:', { workExpId, workExp });

      // 构建动态更新语句
      const updateFields: string[] = [];
      const updateValues: any[] = [];

      // 排除id和user_id字段
      const { id, user_id, ...updateData } = workExp;

      // 遍历updateData的每个属性
      for (const [key, value] of Object.entries(updateData)) {
        updateFields.push(`${key} = ?`);
        // 如果值为undefined，使用null
        updateValues.push(value === undefined ? null : value);
      }

      updateFields.push('updated_at = NOW()');

      // 构建完整的SQL语句
      const query = `UPDATE work_experiences SET ${updateFields.join(', ')} WHERE id = ?`;
      // 添加 workExpId 作为 WHERE 条件的参数
      updateValues.push(workExpId);

      console.log('更新SQL:', query);
      console.log('更新值:', updateValues);

      const [result] = await pool.execute(query, updateValues);

      return (result as any).affectedRows > 0;
    } catch (error) {
      console.error('更新工作经历失败:', error);
      throw error;
    }
  },

  // 删除工作经历
  async deleteWorkExperience(workExpId: number): Promise<boolean> {
    const [result] = await pool.execute(
      'DELETE FROM work_experiences WHERE id = ?',
      [workExpId]
    );

    return (result as any).affectedRows > 0;
  },

  // 获取用户的教育经历
  async getEducationExperiences(userId: number): Promise<EducationExperience[]> {
    const [rows] = await pool.execute(
      'SELECT * FROM education_experiences WHERE user_id = ? ORDER BY start_date DESC',
      [userId]
    );

    return rows as EducationExperience[];
  },

  // 添加教育经历
  async addEducationExperience(eduExp: EducationExperience): Promise<number> {
    try {
      console.log('添加教育经历:', eduExp);

      // 处理可能为undefined的字段，转换为null
      const field_of_study = eduExp.field_of_study || null;
      const start_date = eduExp.start_date || null;
      const end_date = eduExp.end_date || null;
      const description = eduExp.description || null;

      const [result] = await pool.execute(
        `INSERT INTO education_experiences
         (user_id, school, degree, field_of_study, start_date, end_date, description, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
        [eduExp.user_id, eduExp.school, eduExp.degree, field_of_study, start_date, end_date, description]
      );

      return (result as any).insertId;
    } catch (error) {
      console.error('添加教育经历失败:', error);
      throw error;
    }
  },

  // 更新教育经历
  async updateEducationExperience(eduExpId: number, eduExp: Partial<EducationExperience>): Promise<boolean> {
    try {
      console.log('更新教育经历:', { eduExpId, eduExp });

      // 构建动态更新语句
      const updateFields: string[] = [];
      const updateValues: any[] = [];

      // 排除id和user_id字段
      const { id, user_id, ...updateData } = eduExp;

      // 遍历updateData的每个属性，处理可能为undefined的字段
      for (const [key, value] of Object.entries(updateData)) {
        updateFields.push(`${key} = ?`);
        // 如果值为undefined，使用null
        updateValues.push(value === undefined ? null : value);
      }

      updateFields.push('updated_at = NOW()');

      // 构建完整的SQL语句
      const query = `UPDATE education_experiences SET ${updateFields.join(', ')} WHERE id = ?`;
      // 添加 eduExpId 作为 WHERE 条件的参数
      updateValues.push(eduExpId);

      console.log('更新SQL:', query);
      console.log('更新值:', updateValues);

      const [result] = await pool.execute(query, updateValues);

      return (result as any).affectedRows > 0;
    } catch (error) {
      console.error('更新教育经历失败:', error);
      throw error;
    }
  },

  // 删除教育经历
  async deleteEducationExperience(eduExpId: number): Promise<boolean> {
    const [result] = await pool.execute(
      'DELETE FROM education_experiences WHERE id = ?',
      [eduExpId]
    );

    return (result as any).affectedRows > 0;
  },

  // 获取用户的技能
  async getSkills(userId: number): Promise<Skill[]> {
    const [rows] = await pool.execute(
      'SELECT * FROM skills WHERE user_id = ? ORDER BY name ASC',
      [userId]
    );

    return rows as Skill[];
  },

  // 添加技能
  async addSkill(skill: Skill): Promise<number> {
    try {
      console.log('添加技能:', skill);

      const proficiency = skill.proficiency || 3;
      const description = skill.description || null;

      const [result] = await pool.execute(
        `INSERT INTO skills
         (user_id, name, proficiency, description, created_at, updated_at)
         VALUES (?, ?, ?, ?, NOW(), NOW())`,
        [skill.user_id, skill.name, proficiency, description]
      );

      return (result as any).insertId;
    } catch (error) {
      console.error('添加技能失败:', error);
      throw error;
    }
  },

  // 更新技能
  async updateSkill(skillId: number, skill: Partial<Skill>): Promise<boolean> {
    try {
      console.log('更新技能:', { skillId, skill });

      const updateFields: string[] = [];
      const updateValues: any[] = [];

      const { id, user_id, ...updateData } = skill;

      for (const [key, value] of Object.entries(updateData)) {
        updateFields.push(`${key} = ?`);
        // 如果值为undefined，使用null
        updateValues.push(value === undefined ? null : value);
      }

      updateFields.push('updated_at = NOW()');

      // 构建完整的SQL语句
      const query = `UPDATE skills SET ${updateFields.join(', ')} WHERE id = ?`;
      // 添加 skillId 作为 WHERE 条件的参数
      updateValues.push(skillId);

      console.log('更新SQL:', query);
      console.log('更新值:', updateValues);

      const [result] = await pool.execute(query, updateValues);

      return (result as any).affectedRows > 0;
    } catch (error) {
      console.error('更新技能失败:', error);
      throw error;
    }
  },

  // 删除技能
  async deleteSkill(skillId: number): Promise<boolean> {
    const [result] = await pool.execute(
      'DELETE FROM skills WHERE id = ?',
      [skillId]
    );

    return (result as any).affectedRows > 0;
  },

  // 获取用户所有的实习经历
  async getInternships(userId: number): Promise<Internship[]> {
    try {
      const [rows] = await pool.query<RowDataPacket[]>(
        `SELECT * FROM internships WHERE user_id = ?`,
        [userId]
      );

      return rows.map(row => ({
        id: row.id,
        user_id: row.user_id,
        company: row.company,
        position: row.position,
        period: row.period,
        description: row.description
      }));
    } catch (error) {
      console.error('获取用户实习经历失败:', error);
      throw error;
    }
  },

  // 获取特定实习经历详情
  async getInternshipById(internshipId: number, userId: number): Promise<Internship | null> {
    try {
      const [rows] = await pool.query<RowDataPacket[]>(
        `SELECT * FROM internships WHERE id = ? AND user_id = ?`,
        [internshipId, userId]
      );

      if (rows.length === 0) {
        return null;
      }

      const row = rows[0];
      return {
        id: row.id,
        user_id: row.user_id,
        company: row.company,
        position: row.position,
        period: row.period,
        description: row.description
      };
    } catch (error) {
      console.error('获取特定实习经历详情失败:', error);
      throw error;
    }
  },

  // 添加新的实习经历
  async addInternship(internshipData: Omit<Internship, 'id'>): Promise<number> {
    try {
      const [result] = await pool.query<ResultSetHeader>(
        `INSERT INTO internships (user_id, company, position, period, description)
         VALUES (?, ?, ?, ?, ?)`,
        [
          internshipData.user_id,
          internshipData.company,
          internshipData.position,
          internshipData.period,
          internshipData.description
        ]
      );

      return result.insertId;
    } catch (error) {
      console.error('添加实习经历失败:', error);
      throw error;
    }
  },

  // 更新实习经历
  async updateInternship(
    internshipId: number,
    userId: number,
    internshipData: Omit<Internship, 'id' | 'user_id'>
  ): Promise<boolean> {
    try {
      const [result] = await pool.query<ResultSetHeader>(
        `UPDATE internships
         SET company = ?, position = ?, period = ?, description = ?
         WHERE id = ? AND user_id = ?`,
        [
          internshipData.company,
          internshipData.position,
          internshipData.period,
          internshipData.description,
          internshipId,
          userId
        ]
      );

      return result.affectedRows > 0;
    } catch (error) {
      console.error('更新实习经历失败:', error);
      throw error;
    }
  },

  // 删除实习经历
  async deleteInternship(internshipId: number, userId: number): Promise<boolean> {
    try {
      const [result] = await pool.query<ResultSetHeader>(
        `DELETE FROM internships WHERE id = ? AND user_id = ?`,
        [internshipId, userId]
      );

      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除实习经历失败:', error);
      throw error;
    }
  },

  // 获取用户所有的获奖情况
  async getAwards(userId: number): Promise<Award[]> {
    try {
      const [rows] = await pool.query<RowDataPacket[]>(
        `SELECT * FROM awards WHERE user_id = ?`,
        [userId]
      );

      return rows.map(row => ({
        id: row.id,
        user_id: row.user_id,
        name: row.name,
        issuer: row.issuer,
        date: row.date,
        description: row.description
      }));
    } catch (error) {
      console.error('获取用户获奖情况失败:', error);
      throw error;
    }
  },

  // 获取特定奖项详情
  async getAwardById(awardId: number, userId: number): Promise<Award | null> {
    try {
      const [rows] = await pool.query<RowDataPacket[]>(
        `SELECT * FROM awards WHERE id = ? AND user_id = ?`,
        [awardId, userId]
      );

      if (rows.length === 0) {
        return null;
      }

      const row = rows[0];
      return {
        id: row.id,
        user_id: row.user_id,
        name: row.name,
        issuer: row.issuer,
        date: row.date,
        description: row.description
      };
    } catch (error) {
      console.error('获取特定奖项详情失败:', error);
      throw error;
    }
  },

  // 添加新的获奖情况
  async addAward(awardData: Omit<Award, 'id'>): Promise<number> {
    try {
      const [result] = await pool.query<ResultSetHeader>(
        `INSERT INTO awards (user_id, name, issuer, date, description)
         VALUES (?, ?, ?, ?, ?)`,
        [
          awardData.user_id,
          awardData.name,
          awardData.issuer,
          awardData.date,
          awardData.description
        ]
      );

      return result.insertId;
    } catch (error) {
      console.error('添加获奖情况失败:', error);
      throw error;
    }
  },

  // 更新获奖情况
  async updateAward(
    awardId: number,
    userId: number,
    awardData: Omit<Award, 'id' | 'user_id'>
  ): Promise<boolean> {
    try {
      const [result] = await pool.query<ResultSetHeader>(
        `UPDATE awards
         SET name = ?, issuer = ?, date = ?, description = ?
         WHERE id = ? AND user_id = ?`,
        [
          awardData.name,
          awardData.issuer,
          awardData.date,
          awardData.description,
          awardId,
          userId
        ]
      );

      return result.affectedRows > 0;
    } catch (error) {
      console.error('更新获奖情况失败:', error);
      throw error;
    }
  },

  // 删除获奖情况
  async deleteAward(awardId: number, userId: number): Promise<boolean> {
    try {
      const [result] = await pool.query<ResultSetHeader>(
        `DELETE FROM awards WHERE id = ? AND user_id = ?`,
        [awardId, userId]
      );

      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除获奖情况失败:', error);
      throw error;
    }
  },

  // 更新用户密码
  async updatePassword(userId: number, password: string): Promise<boolean> {
    try {
      console.log('更新用户密码:', userId);
      
      const query = `UPDATE users SET password = ?, updated_at = NOW() WHERE id = ?`;
      const values = [password, userId];
      
      console.log('SQL更新语句:', query);
      
      try {
        const [result] = await pool.execute(query, values);
        console.log('SQL更新结果:', result);
        
        return (result as any).affectedRows > 0;
      } catch (sqlError) {
        console.error('SQL执行错误:', sqlError);
        throw new Error(`SQL执行错误: ${(sqlError as Error).message}`);
      }
    } catch (error) {
      console.error('更新用户密码错误:', error);
      throw error;
    }
  },

  // 更新用户企业相关信息
  async updateCompanyInfo(userId: number, companyInfo: { is_company_admin?: boolean, company?: string, company_role?: string }): Promise<boolean> {
    try {
      console.log('更新用户企业信息:', { userId, companyInfo });
      
      // 构建动态更新语句
      const updateFields: string[] = [];
      const updateValues: any[] = [];

      // 遍历companyInfo的每个属性
      for (const [key, value] of Object.entries(companyInfo)) {
        if (value !== undefined) {
          updateFields.push(`${key} = ?`);
          updateValues.push(value);
          console.log(`添加更新字段: ${key} = ${value}`);
        }
      }

      // 没有有效的更新字段，直接返回成功
      if (updateFields.length === 0) {
        console.log('没有有效的更新字段');
        return true;
      }

      // 添加必要的字段
      updateFields.push('updated_at = NOW()');

      // 构建完整的SQL语句
      const query = `UPDATE users SET ${updateFields.join(', ')} WHERE id = ?`;
      updateValues.push(userId); // 放在最后，用于WHERE条件

      console.log('SQL更新语句:', query);
      console.log('更新值:', updateValues);

      try {
        const [result] = await pool.execute(query, updateValues);
        console.log('SQL更新结果:', result);

        return (result as any).affectedRows > 0;
      } catch (sqlError) {
        console.error('SQL执行错误:', sqlError);
        throw new Error(`SQL执行错误: ${(sqlError as Error).message}`);
      }
    } catch (error) {
      console.error('更新用户企业信息错误:', error);
      throw error;
    }
  },

  // 通用的查询方法
  async query<T>(sql: string, params?: any[]): Promise<[T, any]> {
    try {
      // 使用 utils/db 中的 query 函数，以便统一使用 MySQL
      const results = await import('@/utils/db').then(module => module.query<T>(sql, params));
      return [results, null];
    } catch (error) {
      console.error('UserModel.query 错误:', error);
      return [[] as unknown as T, error];
    }
  }
};

export default UserModel;